package jnpf.onlinedev.util.onlineDevUtil;

import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONArray;
import jnpf.base.model.VisualDevJsonModel;
import jnpf.base.util.VisualUtils;
import jnpf.config.ConfigValueUtil;
import jnpf.database.model.entity.DbLinkEntity;
import jnpf.database.util.DataSourceUtil;
import jnpf.database.util.DbTypeUtil;
import jnpf.engine.util.FlowDataUtil;
import jnpf.model.visiual.JnpfKeyConsts;
import jnpf.model.visiual.TableModel;
import jnpf.model.visiual.superQuery.ConditionJsonModel;
import jnpf.onlinedev.model.OnlineDevListModel.*;
import jnpf.util.JsonUtil;
import jnpf.util.StringUtil;
import jnpf.util.UserProvider;
import jnpf.util.context.SpringContext;
import lombok.Cleanup;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 *
 *  生成在线sql语句
 * @author JNPF开发平台组
 * @version V3.2.8
 * @copyright 引迈信息技术有限公司（https://www.jnpfsoft.com）
 * @date  2021/11/8
 */
public class OnlineProductSqlUtils {
	private static DataSourceUtil dataSourceUtil = SpringContext.getBean(DataSourceUtil.class);
	private static ConfigValueUtil configValueUtil = SpringContext.getBean(ConfigValueUtil.class);
	private static UserProvider userProvider = SpringContext.getBean(UserProvider.class);
	private static FlowDataUtil flowDataUtil = SpringContext.getBean(FlowDataUtil.class);

	/**
	 * 生成列表查询sql
	 *
	 * @param sql
	 * @param visualDevJsonModel
	 * @param columnFieldList
	 * @param linkEntity
	 * @return
	 */
	public static OnlineFieldsModel getColumnListSql(StringBuilder sql, VisualDevJsonModel visualDevJsonModel,
																									 List<String> columnFieldList, DbLinkEntity linkEntity) {
		OnlineFieldsModel onlineFieldsModel = new OnlineFieldsModel();
		List<OnlineColumnFieldModel> childFieldList = new ArrayList<>();
		try {
			columnFieldList=columnFieldList.stream().distinct().collect(Collectors.toList());

			@Cleanup Connection conn = VisualUtils.getDataConn(linkEntity);
			List<TableModel> tableModelList = visualDevJsonModel.getVisualTables();
			//主表
			TableModel mainTable = tableModelList.stream().filter(model -> model.getTypeId().equals("1" )).findFirst().orElse(null);
			//获取主键
			Integer primaryKeyPolicy = visualDevJsonModel.getFormData().getPrimaryKeyPolicy();
			String pKeyName = flowDataUtil.getKey(conn, mainTable.getTable(),primaryKeyPolicy);
			//列表中区别子表正则
			String reg = "^[jnpf_]\\S*_jnpf\\S*";

			//列表主表字段
			List<String> mainTableFields = columnFieldList.stream().filter(s -> !s.matches(reg) && !s.toLowerCase().contains("tablefield")).collect(Collectors.toList());
			mainTableFields.add(pKeyName);
			if (visualDevJsonModel.getFormData().getConcurrencyLock()){
				mainTableFields.add("f_version");
			}

			//列表子表字段
			 childFieldList = columnFieldList.stream().filter(s -> s.matches(reg)).map(child -> {
				OnlineColumnFieldModel fieldModel = new OnlineColumnFieldModel();
				String s1 = child.substring(child.lastIndexOf("jnpf_" )).replace("jnpf_", "" );
				String s2 = child.substring(child.indexOf("_" ) + 1, child.lastIndexOf("_jnpf" ));
				fieldModel.setTableName(s2);
				fieldModel.setField(s1);
				fieldModel.setOriginallyField(child);
				return fieldModel;
			}).collect(Collectors.toList());

			//取列表用到的表
			List<String> ColumnTableNameList = childFieldList.stream().map(t -> t.getTableName().toLowerCase()).collect(Collectors.toList());
			List<TableModel> tableModelList1 = tableModelList.stream().filter(t -> ColumnTableNameList.contains(t.getTable().toLowerCase())).collect(Collectors.toList());
			List<OnlineColumnChildFieldModel> classifyFieldList = new ArrayList<>(10);
			for (TableModel t : tableModelList1) {
				OnlineColumnChildFieldModel childFieldModel = new OnlineColumnChildFieldModel();
				childFieldModel.setTable(t.getTable());
				childFieldModel.setRelationField(t.getRelationField());
				childFieldModel.setTableField(t.getTableField());
				classifyFieldList.add(childFieldModel);
			}
			//取别名
			for (int i = 0; i < childFieldList.size(); i++) {
				OnlineColumnFieldModel model = childFieldList.get(i);
				model.setOtherName(model.getField() + "_" + i);
			}

			String mainSqlFields = mainTableFields.stream().map(m -> mainTable.getTable() + "." + m).collect(Collectors.joining("," ));
			String childFields = "";
			if (classifyFieldList.size() > 0) {
				childFields = "," + childFieldList.stream().map(c -> c.getTableName() + "." + c.getField() + "  " + c.getOtherName()).collect(Collectors.joining("," ));
			}
			OnlineListSqlModel listSqlModel = new OnlineListSqlModel();
			listSqlModel.setFields(mainSqlFields + childFields);
			listSqlModel.setMainTable(mainTable.getTable());
			listSqlModel.setPKeyName(pKeyName);

			//获取查询语句
			sql = getSqlByDatabase(classifyFieldList, listSqlModel, linkEntity);
			onlineFieldsModel.setMastTableList(childFieldList);
		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
		}
		onlineFieldsModel.setSql(sql);
		return onlineFieldsModel;
	}

	public static StringBuilder getSqlByDatabase(List<OnlineColumnChildFieldModel> classifyFieldList, OnlineListSqlModel listSqlModel, DbLinkEntity linkEntity) {
		StringBuilder sql = new StringBuilder();
		DataSourceUtil dataSourceUtil = SpringContext.getBean(DataSourceUtil.class);
		String mainTable = listSqlModel.getMainTable();

		if ("PostgreSQL".equals(dataSourceUtil.getDbType()) && configValueUtil.isMultiTenancy()) {
			mainTable = userProvider.get().getTenantDbConnectionString() + "." + mainTable;
		}

//		//金仓 oracle 需关闭大小写敏感
//		//数据源
//		if (linkEntity != null) {
//			if (DbTypeUtil.checkKingbase(linkEntity)) {
//				mainTable = linkEntity.getUserName() + "." + listSqlModel.getMainTable();
//			}
//		}
//		//默认数据源
//		else {
//			if (DbTypeUtil.checkKingbase(dataSourceUtil)) {
//				mainTable = linkEntity.getUserName() + "." + listSqlModel.getMainTable();
//			}
//		}
		{
			if (classifyFieldList.size() > 0) {
				sql.append("select distinct " + listSqlModel.getFields() + " from" + " " + mainTable);
//				for (OnlineColumnChildFieldModel model : classifyFieldList) {
//					sql.append(" LEFT JOIN " + model.getTable() + " ON " + model.getTable() + "." + model.getTableField() + "=" + mainTable + "." + model.getRelationField() + " " );
//				}
			} else {
				sql.append("select distinct " + listSqlModel.getFields() + " from" + " " + mainTable);
			}
		}
		return sql;
	}

	/**
	 *
	 */
	public static StringBuilder getConditionSql(StringBuilder sql, DbLinkEntity linkEntity, List<VisualColumnSearchVO> searchVOList,List<Object> objList) {

		boolean isOracle = DbTypeUtil.checkOracle(dataSourceUtil);
		if (linkEntity != null) {
			isOracle = linkEntity.getDbType().toLowerCase().contains("oracle" );
		}

		for (int k = 0; k < searchVOList.size(); k++) {
			VisualColumnSearchVO vo = searchVOList.get(k);
			String jnpfKey = "jnpfkey";
			if (Objects.nonNull(vo.getConfig())){
				jnpfKey = vo.getConfig().getJnpfKey();
			}
			String tableName = vo.getTable();
			String searchType = vo.getSearchType();
			String vModel = vo.getField();
			String value = String.valueOf(vo.getValue());
			String format;

			Boolean isMultiple = vo.getMultiple() != null ? vo.getMultiple() : false;
			if (isMultiple || JnpfKeyConsts.CHECKBOX.equals(jnpfKey) || JnpfKeyConsts.CASCADER.equals(jnpfKey)
					|| JnpfKeyConsts.CURRORGANIZE.equals(jnpfKey)  || JnpfKeyConsts.ADDRESS.equals(jnpfKey)) {
				searchType = "2";
				if (value.contains("[")){
					List<String> mutipleList = JsonUtil.getJsonToList(value, String.class);
					value = mutipleList.get(mutipleList.size()-1);
				}
			}
			if(JnpfKeyConsts.COM_INPUT.equals(jnpfKey) || JnpfKeyConsts.TEXTAREA.equals(jnpfKey)){
				if ("3".equals(searchType)){
					searchType = "2";
				}
			}
			if ("1".equals(searchType)) {
				sql.append("AND " + tableName + "." + vModel + " = " + "?" );
				objList.add(value);
			} else if ("2".equals(searchType)) {
				sql.append("AND " + tableName + "." + vModel + " like " + "?" );
				objList.add("%" + value + "%");
			} else if ("3".equals(searchType)) {
				switch (jnpfKey) {
					case JnpfKeyConsts.MODIFYTIME:
					case JnpfKeyConsts.CREATETIME:
					case JnpfKeyConsts.DATE:
						JSONArray timeStampArray = (JSONArray) vo.getValue();
						Long o1 = (Long) timeStampArray.get(0);
						Long o2 = (Long) timeStampArray.get(1);
						format = StringUtil.isEmpty(vo.getFormat()) ? "yyyy-MM-dd HH:mm:ss" : vo.getFormat();
						//时间戳转string格式
						String startTime = OnlinePublicUtils.getDateByFormat(o1, format);
						String endTime = OnlinePublicUtils.getDateByFormat(o2, format);
						//处理时间查询条件范围
						if (!"yyyy-MM-dd HH:mm:ss".equals(format) || vo.getFormat()==null){
							endTime = endTime.substring(0, 10);
						}
						String firstTimeDate = OnlineDatabaseUtils.getTimeFormat(startTime);
						String lastTimeDate = OnlineDatabaseUtils.getLastTimeFormat(endTime);

						if (isOracle) {
							sql.append("AND " + tableName + "." + vModel + " between to_date(" + "?,'yyyy-mm-dd hh24:mi:ss'" + ") AND to_date(" + "?,'yyyy-mm-dd hh24:mi:ss')" );
						} else {
							sql.append("AND " + tableName + "." + vModel + " between " + "?" + "AND " + "?" );
						}
						objList.add(firstTimeDate);
						objList.add(lastTimeDate);
						break;
					case JnpfKeyConsts.TIME:
						List<String> stringList = JsonUtil.getJsonToList(value, String.class);
						format = "HH:mm:ss";
						if (isOracle) {
							sql.append("AND " + tableName + "." + vModel + " between to_date(" + "?,'" + format + "'" + ") AND to_date(" + "?,'" + format + "')" );
						} else {
							sql.append("AND " + tableName + "." + vModel + " between " + "?" + "AND " + "?" );
						}
						objList.add(stringList.get(0));
						objList.add(stringList.get(1));
						break;
					case JnpfKeyConsts.NUM_INPUT:
					case JnpfKeyConsts.CALCULATE:
						Float firstValue = null;
						Float secondValue = null;
						JSONArray objects = (JSONArray) vo.getValue();
						for (int i = 0; i < objects.size(); i++) {
							Object n = objects.get(i);
							if (ObjectUtil.isNotEmpty(n)) {
								if (i == 0) {
									firstValue = Float.parseFloat(String.valueOf(n));
								} else {
									secondValue = Float.parseFloat(String.valueOf(n));
								}
							}
						}
						if (firstValue != null) {
							sql.append("AND " + tableName + "." + vModel + " >= ?" );
							objList.add(firstValue);
						}
						if (secondValue != null) {
							sql.append("AND " + tableName + "." + vModel + " <= ?");
							objList.add(secondValue);
						}
						break;
					default:
						break;
				}
			}
		}
		return sql;
	}

	public static StringBuilder getConditionSql(String childTableName, DbLinkEntity linkEntity, List<VisualColumnSearchVO> searchVOList,List<Object> childTableObjs) {
		StringBuilder sql = new StringBuilder();
		boolean isOracle = DbTypeUtil.checkOracle(dataSourceUtil);
		if (linkEntity != null) {
			isOracle = linkEntity.getDbType().toLowerCase().contains("oracle" );
		}

		for (int k = 0; k < searchVOList.size(); k++) {
			VisualColumnSearchVO vo = searchVOList.get(k);
			String jnpfKey = "jnpfkey";
			if (Objects.nonNull(vo.getConfig())){
				jnpfKey = vo.getConfig().getJnpfKey();
			}
			String tableName = vo.getTable();
			if (!childTableName.equalsIgnoreCase(tableName)){
				continue;
			}
			String searchType = vo.getSearchType();
			String vModel = vo.getField();
			String value = String.valueOf(vo.getValue());
			String format;

			Boolean isMultiple = vo.getMultiple() != null ? vo.getMultiple() : false;
			if (isMultiple || JnpfKeyConsts.CHECKBOX.equals(jnpfKey) || JnpfKeyConsts.CASCADER.equals(jnpfKey)
					|| JnpfKeyConsts.CURRORGANIZE.equals(jnpfKey) || JnpfKeyConsts.COMSELECT.equals(jnpfKey) || JnpfKeyConsts.ADDRESS.equals(jnpfKey)) {
				searchType = "2";
				if (value.contains("[")){
					List<String> mutipleList = JsonUtil.getJsonToList(value, String.class);
					value = mutipleList.get(mutipleList.size()-1);
				}
			}
			if(JnpfKeyConsts.COM_INPUT.equals(jnpfKey) || JnpfKeyConsts.TEXTAREA.equals(jnpfKey)){
				if ("3".equals(searchType)){
					searchType = "2";
				}
			}
			if ("1".equals(searchType)) {
				sql.append("AND " + tableName + "." + vModel + " = ?" );
				childTableObjs.add(value);
			} else if ("2".equals(searchType)) {
				sql.append("AND " + tableName + "." + vModel + " like ?" );
				childTableObjs.add("%" + value + "%");
			} else if ("3".equals(searchType)) {
				switch (jnpfKey) {
					case JnpfKeyConsts.MODIFYTIME:
					case JnpfKeyConsts.CREATETIME:
					case JnpfKeyConsts.DATE:
						JSONArray timeStampArray = (JSONArray) vo.getValue();
						Long o1 = (Long) timeStampArray.get(0);
						Long o2 = (Long) timeStampArray.get(1);
						format = StringUtil.isEmpty(vo.getFormat()) ? "yyyy-MM-dd HH:mm:ss" : vo.getFormat();
						//时间戳转string格式
						String startTime = OnlinePublicUtils.getDateByFormat(o1, format);
						String endTime = OnlinePublicUtils.getDateByFormat(o2, format);
						//处理时间查询条件范围
						if (!"yyyy-MM-dd HH:mm:ss".equals(format) || vo.getFormat()==null){
							endTime = endTime.substring(0, 10);
						}
						String firstTimeDate = OnlineDatabaseUtils.getTimeFormat(startTime);
						String lastTimeDate = OnlineDatabaseUtils.getLastTimeFormat(endTime);

						if (isOracle) {
							sql.append("AND " + tableName + "." + vModel + " between to_date(" + "?,'yyyy-mm-dd hh24:mi:ss'" + ") AND to_date(" + "?,'yyyy-mm-dd hh24:mi:ss')" );
						} else {
							sql.append("AND " + tableName + "." + vModel + " between " + "?" + "AND " + "?" );
						}
						childTableObjs.add(firstTimeDate);
						childTableObjs.add(lastTimeDate);
						break;
					case JnpfKeyConsts.TIME:
						List<String> stringList = JsonUtil.getJsonToList(value, String.class);
						format = "HH:mm:ss";
						if (isOracle) {
							sql.append("AND " + tableName + "." + vModel + " between to_date(" + "?,'" + format + "'" + ") AND to_date(" + "?,'" + format + "')" );
						} else {
							sql.append("AND " + tableName + "." + vModel + " between " + "?" + "AND " + "?" );
						}
						childTableObjs.add(stringList.get(0));
						childTableObjs.add(stringList.get(1));
						break;
					case JnpfKeyConsts.NUM_INPUT:
					case JnpfKeyConsts.CALCULATE:
						Float firstValue = null;
						Float secondValue = null;
						JSONArray objects = (JSONArray) vo.getValue();
						for (int i = 0; i < objects.size(); i++) {
							Object n = objects.get(i);
							if (ObjectUtil.isNotEmpty(n)) {
								if (i == 0) {
									firstValue = Float.parseFloat(String.valueOf(n));
								} else {
									secondValue = Float.parseFloat(String.valueOf(n));
								}
							}
						}
						if (firstValue != null) {
							sql.append("AND " + tableName + "." + vModel + " >= ?");
							childTableObjs.add(firstValue);
						}
						if (secondValue != null) {
							sql.append("AND " + tableName + "." + vModel + " <= ?");
							childTableObjs.add(secondValue);
						}
						break;
					default:
						break;
				}
			}
		}
		return sql;
	}

	/**
	 * 高级查询哦
	 * @param mainSql
	 * @param superQueryList
	 * @param matchLogic
	 * @return
	 */
	public static StringBuilder getSuperSql(StringBuilder mainSql, List<ConditionJsonModel> superQueryList, String matchLogic,List<Object> objs){
		String reg = "^[jnpf_]\\S*_jnpf\\S*";
		superQueryList.stream().filter(condition->condition.getField().matches(reg)).forEach(su->{
					su.setTableName(su.getField().substring(su.getField().indexOf("_" ) + 1, su.getField().lastIndexOf("_jnpf" )));
					su.setField(su.getField().substring(su.getField().lastIndexOf("jnpf_" )).replace("jnpf_", "" ));
				}
		);
		String op;
		for (int i =0; i< superQueryList.size(); i++){
			ConditionJsonModel conditionJsonModel = superQueryList.get(i);
			String fieldValue =conditionJsonModel.getFieldValue();
			String symbol = getSymbol(conditionJsonModel.getSymbol());
			boolean isEmpty = StringUtil.isEmpty(fieldValue) || "[]".equals(fieldValue);
			if (isEmpty){
				if (symbol.equals("not like")){
					symbol = "<>";
				} else if (symbol.equals("like")){
					symbol = "=";
				}
			}
			String tableName = conditionJsonModel.getTableName();
			String jnpfKey = conditionJsonModel.getJnpfKey();
			if (i== 0){
				op = "AND";
			} else {
				op = matchLogic;
			}
			if (jnpfKey.equals(JnpfKeyConsts.DATE) || jnpfKey.equals(JnpfKeyConsts.CREATETIME) || jnpfKey.equals(JnpfKeyConsts.MODIFYTIME)){
				String format = "yyyy-MM-dd HH:mm:ss" ;
				if (fieldValue!=null){
					Long o1 = Long.valueOf(fieldValue);
					String startTime = OnlinePublicUtils.getDateByFormat(o1, format);
					fieldValue = startTime;
				}
			}else if (jnpfKey.equals(JnpfKeyConsts.CURRORGANIZE)){
				if (fieldValue!=null){
					List<String> jsonToList = JsonUtil.getJsonToList(fieldValue, String.class);
					fieldValue = jsonToList.get(jsonToList.size()-1);
				}
			}
			if (symbol.equals("not like") || symbol.equals("like")){
				fieldValue = "%" + fieldValue + "%";
			}
			boolean isNull = false;
			boolean isAppend = false;
			if (isEmpty){
				if (symbol.equals("not like") || symbol.equals("<>")){
					isAppend = true;
					isNull = false;
				} else if (symbol.equals("like")||symbol.equals("=")){
					isAppend = true;
					isNull = true;
				}
			} else {
				if (symbol.equals("not like") || symbol.equals("<>")){
					isAppend = true;
					isNull = true;
				}
			}
			String brackets = " ";
			if (isAppend){
				brackets = " ( ";
			}
			String closer = " ) ";
			if (jnpfKey.equals(JnpfKeyConsts.NUM_INPUT) || jnpfKey.equals(JnpfKeyConsts.CALCULATE)){
				if (fieldValue!=null){
						mainSql.append(" " + op + brackets + tableName+"."+ conditionJsonModel.getField() + " " + symbol + " "  +  "?");
						objs.add(fieldValue);
				}else {
					closer = " ";
				}
			}else {
				if (fieldValue==null){
					fieldValue ="";
				}
				mainSql.append(" " + op + brackets + tableName+"."+ conditionJsonModel.getField() + " " + symbol + " " + "?");
				objs.add(fieldValue);
			}

			if (isAppend){
				String not = isNull ? "" : "not";
				String anOP =" or ";
				if (isEmpty && symbol.equals("<>")){
					anOP = " and ";
				}
				if (StringUtil.isEmpty(closer)){
					anOP = " and ";
				}
				mainSql.append(anOP + tableName+"."+ conditionJsonModel.getField() + " is "+ not  +" Null"+closer);
			}
		}
		return mainSql;
	}


	public static StringBuilder getSuperSql(List<ConditionJsonModel> superQueryList, String matchLogic,String childTableName,List<Object> objs){
		StringBuilder mainSql = new StringBuilder();
		String reg = "^[jnpf_]\\S*_jnpf\\S*";
		superQueryList.stream().filter(condition->condition.getField().matches(reg)).forEach(su->{
					su.setTableName(su.getField().substring(su.getField().indexOf("_" ) + 1, su.getField().lastIndexOf("_jnpf" )));
					su.setField(su.getField().substring(su.getField().lastIndexOf("jnpf_" )).replace("jnpf_", "" ));
				}
		);
		String op;
		for (int i =0; i< superQueryList.size(); i++){
			ConditionJsonModel conditionJsonModel = superQueryList.get(i);
			String fieldValue = conditionJsonModel.getFieldValue();
			String symbol = getSymbol(conditionJsonModel.getSymbol());
			String tableName = conditionJsonModel.getTableName();
			boolean isEmpty = StringUtil.isEmpty(fieldValue) || "[]".equals(fieldValue);
			if (isEmpty){
				if (symbol.equals("not like")){
					symbol = "<>";
				} else if (symbol.equals("like")){
					symbol = "=";
				}
			}
			if (!childTableName.equalsIgnoreCase(tableName)){
				continue;
			}
			String jnpfKey = conditionJsonModel.getJnpfKey();
			if (i== 0){
				op = "AND";
			} else {
				op = matchLogic;
			}
			if (jnpfKey.equals(JnpfKeyConsts.DATE) || jnpfKey.equals(JnpfKeyConsts.CREATETIME) || jnpfKey.equals(JnpfKeyConsts.MODIFYTIME)){
				String format = "yyyy-MM-dd HH:mm:ss" ;
				if (fieldValue!=null){
					Long o1 = Long.valueOf(fieldValue);
					String startTime = OnlinePublicUtils.getDateByFormat(o1, format);
					fieldValue = startTime;
				}else {
					fieldValue ="";
				}
			}else if (jnpfKey.equals(JnpfKeyConsts.CURRORGANIZE)){
				if (fieldValue!=null){
					List<String> jsonToList = JsonUtil.getJsonToList(fieldValue, String.class);
					fieldValue = jsonToList.get(jsonToList.size()-1);
				}else {
					fieldValue = "";
				}
			}
			if (symbol.equals("not like") || symbol.equals("like")){
				fieldValue = "%" + fieldValue + "%";
			}
			boolean isNull = false;
			boolean isAppend = false;
			if (isEmpty){
				if (symbol.equals("not like") || symbol.equals("<>")){
					isAppend = true;
					isNull = false;
				} else if (symbol.equals("like")||symbol.equals("=")){
					isAppend = true;
					isNull = true;
				}
			} else {
				if (symbol.equals("not like") || symbol.equals("<>")){
					isAppend = true;
					isNull = true;
				}
			}
			String brackets = " ";
			if (isAppend){
				brackets = " ( ";
			}
			String closer = " ) ";
			if (jnpfKey.equals(JnpfKeyConsts.NUM_INPUT) || jnpfKey.equals(JnpfKeyConsts.CALCULATE)){
				if (fieldValue!= null){
					mainSql.append(" " + op + brackets + tableName+"."+ conditionJsonModel.getField() + " " + symbol + " "  + "?" );
					objs.add(fieldValue);
				} else {
					closer = " ";
				}
			}else {
				if (fieldValue==null){
					fieldValue ="";
				}
				mainSql.append(" " + op + brackets + tableName+"."+ conditionJsonModel.getField() + " " + symbol + " " + "?");
				objs.add(fieldValue);
			}

			if (isAppend){
				String not = isNull ? "" : "not";
				String anOP =" or ";
				if (isEmpty && symbol.equals("<>")){
					anOP = " and ";
				}
				if (StringUtil.isEmpty(closer)){
					anOP = " and ";
				}
				mainSql.append(anOP + tableName+"."+ conditionJsonModel.getField() + " is "+ not  +" Null" + closer);
			}
		}
		return mainSql;
	}

	private static String getSymbol(String symbol){
		if ("==".equals(symbol)){
			return "=";
		}else if ("notLike".equals(symbol)){
			return "not like";
		}else {
			return symbol;
		}
	}

	public static List<OnlineColumnFieldModel> getChildValue(List<String> collect) {
		List<OnlineColumnFieldModel> childFieldList =new ArrayList<>() ;
			//列表中区别子表正则
			String reg = "^[jnpf_]\\S*_jnpf\\S*";

			//列表主表字段
			List<String> mainTableFields = collect.stream().filter(s -> !s.matches(reg)).collect(Collectors.toList());
			List<OnlineColumnFieldModel> mainList = mainTableFields.stream().map(c -> {
				OnlineColumnFieldModel fieldModel = new OnlineColumnFieldModel();
				fieldModel.setOriginallyField(c);
				fieldModel.setOtherName(c);
				return fieldModel;
			}).collect(Collectors.toList());

			//列表子表字段
			childFieldList = collect.stream().filter(s -> s.matches(reg)).map(child -> {
				OnlineColumnFieldModel fieldModel = new OnlineColumnFieldModel();
				String s1 = child.substring(child.lastIndexOf("jnpf_" )).replace("jnpf_", "" );
				String s2 = child.substring(child.indexOf("_" ) + 1, child.lastIndexOf("_jnpf" ));
				fieldModel.setTableName(s2);
				fieldModel.setField(s1);
				fieldModel.setOriginallyField(child);
				return fieldModel;
			}).collect(Collectors.toList());

			//取别名
			for (int i = 0; i < childFieldList.size(); i++) {
				OnlineColumnFieldModel model = childFieldList.get(i);
				model.setOtherName(model.getField() + "_" + i);
			}
			childFieldList.addAll(mainList);
			return childFieldList;
		}

		public static StringBuilder getJoinTableSql(List<TableModel> tableModels,String pkeyId){
		StringBuilder JoinSql = new StringBuilder();
		TableModel mainTable = tableModels.stream().filter(model -> model.getTypeId().equals("1")).findFirst().orElse(null);
			for (TableModel model : tableModels){
			if (model.getTypeId().equals("0")){
				JoinSql.append(" LEFT JOIN " + model.getTable() + " ON " + model.getTable() + "." + model.getTableField() + "=" + mainTable.getTable() + "." + pkeyId + " " );
			}
		}
       return JoinSql;
		}

		public static Map<String,String> getChildDataValue(List<TableModel> childTableModels, List<String> tableFields,
																		 Map<String,String> tableFieldAndTableName, Map<String,String> tableNameAndTableField){
			Map<String, List<String>> tableMap = tableFields.stream().collect(Collectors.groupingBy(t -> t.substring(0, t.lastIndexOf("-"))));
			Map<String,String> sqlMap = new HashMap<>();
			for (TableModel tableModel : childTableModels){
				String table = tableModel.getTable();
				String tableField = tableNameAndTableField.get(table);
				String relationID = tableModel.getRelationField();

				List<String> childFields = tableMap.get(tableField);

				String collect = childFields.stream().map(c -> {
					String childT = c.substring(0, c.lastIndexOf("-"));
					String childF = c.substring(c.lastIndexOf("-")+ 1);
					return tableFieldAndTableName.get(childT) + "." + childF;
				}).collect(Collectors.joining(","));
				collect = "select " + relationID + ", " + collect + " from " + table + " where " + table + "." + relationID + " in";
				sqlMap.put(tableField,collect);
			}
		return sqlMap;
		}
}